The present invention relates to the field of multi-dimensional databases. In particular, this invention relates to an On-Line Analytical Processing (OLAP) query generation engine for generating OLAP queries for use with different OLAP servers.
On-Line Analytical Processing (OLAP) database servers provide a platform for high-end analytics and decision support applications. OLAP technology allows organizations (i.e., enterprises) to extract data from multiple, disparate transactional and operational systems into homogenous, aggregated repositories of information for the purpose of high-performance analysis and reporting. The process of moving data out of core transactional and operational systems into an OLAP environment typically includes cleansing the data by enforcing consistency and data integrity, and aggregating the data using like keys. This process is known as data transformation. The result of the data transformation process is a multi-dimensional OLAP database, known as an xe2x80x9cOLAP cubexe2x80x9d, which is highly optimized for reporting and analysis.
OLAP cubes are typically subject-matter oriented, with the data in the cube being organized by a series of dimensions. The dimensions are hierarchical in nature (e.g., year, quarter, month, etc.) to allow for multiple levels of data aggregation. An end user can thus choose whether to analyze data in an OLAP cube at varying levels of data aggregation, such as at summary, intermediate and detailed analysis levels. OLAP data cubes with different subject matters typically have different numbers of dimensions with different hierarchical designs. OLAP cubes can also share one or more dimensions to provide a homogenous view of the structure of the cube data amongst different cubes. The concept of keying cube data by dimensions, known as dimensional modeling, allows for a simplified approach to reporting and analysis of data. In particular, users need only understand the dimensions of the OLAP cubes and their hierarchies, and are insulated from the need to understand the underlying physical implementation of the database schema that is used to store the data. Thus, users are presented with a logical view of the information stored in the cube, and can create reports and queries without having to perform complex joins or any other function which would require an understanding of the underlying database structure.
The leading OLAP database servers on the market today are the Hyperion Essbase(trademark) OLAP server, available from Hyperion Solutions Corp. of CA, and the Microsoft Analysis Services OLAP server, available from Microsoft Corp. of WA. Both servers provide the ability to build subject matter cubes using dimensional modeling techniques which support hierarchies, calculated members, aggregations, and high-performance reporting and analysis. From a querying standpoint, both servers offer similar features, but with very different implementations. In particular, the structured query formats used for the two database servers are very different. The query language used for the Microsoft Analysis Services OLAP server is known as Multi-Dimensional Expression Query Language (xe2x80x9cMDXxe2x80x9d). The MDX query language leverages Microsoft""s OLE database (xe2x80x9cOLEDBxe2x80x9d) for OLAP standard, and follows a Structured Query Language (SQL)-like syntax with special extensions that are used for OLAP cube querying. The special extensions are needed since SQL is only two-dimensional, while OLAP data cubes are n-dimensional. The query language that is used for the Hyperion Essbase(trademark) OLAP server is known as Report Scripts (xe2x80x9cRSxe2x80x9d). Although the RS query language provides a full range of querying options within the Hyperion Essbase(trademark) environment, the syntax of queries for RS is very different from the syntax of queries for MDX. For example, exemplary MDX and RS query statements 100 are shown in FIG. 1 (note the data cubes used for the two examples contained different data). A comparison shows that the syntax used to create these query statements is very different. The differences between these formats makes it difficult for OLAP query and reporting applications to support both Microsoft Analysis Services and Hyperion Essbase(trademark) OLAP servers from the same code base.
Along with the Microsoft Analysis Services and Hyperion Essbase(trademark) OLAP database servers, there are other OLAP servers on the market today that use other structured query formats. Further, there are likely to be still other OLAP servers on the market in the future that will use still other structured query formats, including formats that are not yet known. The differences in structured query formats between these present and future OLAP servers makes it even more difficult for OLAP query and reporting applications to support any or all of these different servers. For example, it would be very difficult to design an OLAP query and reporting application that can be easily adapted to support a future OLAP server that will use a structured query format that is as yet unknown.
In the relational database world, the SQL and Open Database Connectivity (xe2x80x9cODBCxe2x80x9d) standards are industry-standard methodologies for accessing relational databases. Thus, query and reporting applications in the relational database world can implement SQL and ODBC methodologies to access relational databases across relational database vendors. Unfortunately, industry-standard methodologies do not exist for use with OLAP database technology. In particular, there are currently no SQL or ODBC-type standards that simplify the generation of OLAP queries. Thus, it is difficult for an OLAP query and reporting application to support both Microsoft Analysis Services and Hyperion Essbase(trademark) servers from one code base, or to support other combinations of OLAP servers that use, or will use, different structured query formats.
Thus, there is a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to efficiently and easily support multiple OLAP database servers that use different structured query formats. For example, there is a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to support both Microsoft Analysis Services and Hyperion Essbase(trademark) database servers from one code base. There is also a need for a method and apparatus for generating OLAP queries which can be used by OLAP query and reporting applications to support other OLAP servers on the market today that use different structured query formats, or that can be easily adapted to support OLAP servers that will be on the market in the future.
In accordance with one embodiment of the present invention, a method of generating an On-Line Analytical Processing (OLAP) query includes providing a query object capable of supporting a plurality of OLAP servers (each server using a different structured query format), determining an OLAP server from among the plurality of OLAP servers for which the query will be executed based upon a property of the query object, and processing the query object to generate a query statement using the structured query format which corresponds to the OLAP server that was determined.
In one embodiment of this method, the query statement is generated using the Multi-Dimensional Expression (MDX) query format when the property of the query object indicates the OLAP server is a Microsoft Analysis Services OLAP server. In this embodiment, processing the query object may include generating a select clause and a from clause from the query object, and combining the select and the from clauses to generate the query statement. Processing the query object may also include generating a with clause, a where clause and/or an order by clause, and combining any of these other clauses with the select clause and the from clause.
In another embodiment of this method, the query statement is generated using the Report Scripts (RS) query format when the property of the query object indicates the server is an Hyperion Essbase(trademark) OLAP server. In this embodiment, processing the query object may include generating a header specification, an axis specification and an execute specification from the query object, and combining the header, axis and execute specifications. The processing may also include generating a member specification and/or an expression specification, and combining either or both of these other specifications with the header, axis and execute specifications.
In another embodiment of this method, the query object can support at least three OLAP servers, and each of these OLAP servers uses a different structured query format.
In accordance with another embodiment of the present invention, an apparatus for generating an OLAP query using a query object capable of supporting a plurality of OLAP servers (each server using a different structured query format) includes means for determining an OLAP server from among the plurality of OLAP servers based upon a property of the query object, and means for processing the query object to generate a query statement using the structured query format which corresponds to the OLAP server that was determined.
In one embodiment of this apparatus, the query object can support the Microsoft Analysis Services OLAP server and the Hyperion Essbase(trademark) OLAP server, and the means for processing generates an MDX query statement and an RS query statement when the property indicates that the OLAP server is a Microsoft Analysis Services OLAP server and an Hyperion Essbase(trademark) OLAP server, respectively. In another embodiment of this apparatus, the query object is capable of supporting at least three OLAP servers which each uses a different structured query format, and the means for processing generates the query statement using one of the at least three formats.
In accordance with another embodiment of the present invention, an OLAP query generation engine includes a query object model with a data structure which models an OLAP query, and a programming interface for generating an OLAP query statement from the query object model according to a structured query format specified by the query object model. The data structure may model the OLAP query in an abstract form that is compatible with multiple OLAP structured query formats, or in a form abstract from implementation of the underlying OLAP servers. The programming interface may also maintain or execute the query statement. In one embodiment of the engine, the query object model can specify the MDX and RS query formats, and the programming interface generates an MDX query statement and an RS query statement when the query object model specifies the MDX query format and the RS query format, respectively. In another embodiment, the query object model can specify first, second and third structured query formats, and the programming interface generates the query statement using the first, second and third formats when the query object model specifies the first, second and third structured query formats, respectively.
In accordance with another embodiment of the present invention, an OLAP query object model includes a query object that defines an OLAP query in abstract form, and a root object including methods for transforming the query object into an OLAP query statement according to a structured query format specified by the query object. In one embodiment of this model, the query object can specify the MDX query format and the RS query format, and the root object includes methods for generating an MDX query statement and an RS query statement when the query object specifies the MDX query format and the RS query format, respectively. In another embodiment of this model, the query object is capable of specifying first, second and third structured query formats, and the root object includes methods for generating the query statement using the first, second and third structured query formats when the query object specifies the first, second and third structured query formats, respectively.
In accordance with another embodiment of the present invention, an OLAP query generation engine is for use with an OLAP query and reporting application that supports first and second OLAP servers using first and second structured query formats. The engine includes an object model having a data structure that models an OLAP query, and a programming interface for generating an OLAP query statement according to the first structured query format when the first structured query format is specified by the object model and according to the second structured query format when the second structured query format is specified. In one embodiment of this engine, the application supports a Microsoft Analysis Services OLAP server using the MDX query format and an Hyperion Essbase(trademark) OLAP server using the RS query format, and the programming interface generates an MDX query statement and an RS query statement when the object model specifies the MDX query format and RS query format, respectively. In another embodiment of this engine, the query and reporting application supports first, second and third OLAP servers using first, second and third structured query formats, and the programming interface generates the query statement using the first, second and third structured query formats when the first, second and third structured query formats are specified.